/**
 * 
 */
package org.cnbi.utils.pojo;

import java.io.Serializable;
import java.util.Map;

import org.cnbi.entity.Compose;
import org.cnbi.entity.Subject;
import org.cnbi.utils.string.StringUtil;
import org.cnbi.utils.tools.Constants;
import org.cnbi.utils.tools.GlobalConstants;
import org.cnbi.utils.tools.SymbolConstants;

/**
 * Copyright © 2014中国经邦. All rights reserved.
 * @author 龚佳新
 * @date 2014年4月22日下午4:28:20
 * @cnbi-utils
 * 按主题生成sql公共类
 */
public class SqlGenerateBean implements Serializable{
	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	
	
	public SubjectSqlBean bean ;
	
	private DisplayBean display ;
	/**
	 * 传入相关维度的集合
	 */
	private Map<String, String> conditionMap;
	/**
	 * 传入的查询事实列集合
	 */
	private String[] queryFact;
	/**
	 * 显示相关维度的列 当showDim的长度为0时，显示的所有维度+事实列
	 */
	private String[] showDim =new String[] { "Item" };
	/**
	 * 数据类型
	 * 1月度数据
	 * 2季度数据
	 * 3年度数
	 */
	private int type = 1;//1月度数据   2季度数据  3年度数

	/**
	 * 行列转置的维度名称
	 */
	private String povitDim;
	/**
	 * 
	 */
	
	/**
	 * 事实列的map
	 */
	private Map<String, String> measureMap;
	private String orderByDim [] = {"DW_DimItem&ssortby"};
	
	private String groupByDim [] = {"dim_Item"};
	
	public SqlGenerateBean(){}
	
	public SqlGenerateBean(SubjectSqlBean sb){
		this.bean =sb;
	}
	
	/**
	 * @return the type
	 */
	public int getType() {
		return type;
	}

	/**
	 * @param type the type to set
	 */
	public void setType(int type) {
		this.type = type;
	}

	private void set_fact(String ds,StringBuffer sql,Compose compose,DisplayBean displayBean){
		String field = ds+compose.getField();boolean showCode = displayBean.isShowScode();
		if(null !=povitDim){//如果行列转置或者如果显示编码
			sql.append(field);
		}else{
			/**
			 * 如果显示编码
			 */
			if(showCode){
				sql.append(field+" as id");
			}
			/**
			 * 如果显示名称
			 */
			if(displayBean.isShowSname()){
				if(showCode){
					 sql.append(",");
				}
				sql.append("(select sname from ").append(compose.getFactTable()).append(" where scode =").append(field).append(") as \""+compose.getSname()+"\"");
			}
		}
    	sql.append(",");
	}
	/**
	 * 默认为显示名称的sql
	 * @return
	 */
	public String getSql(){
		if(null == getDisplay()){
			display = new DisplayBean();
		}
		return getSql(display);
	}
	private String factSplit =Constants.FACT+SymbolConstants.UNDERLINE;
	/**
	 * 当为非月度数据时，即统计数据时 ，有些度量是不要相加的
	 * 如：累计C,预算累计E
	 * eg:1季度的累计=1月本期+2月本期+3月本期  也等于1季度的本期
	 * @param sql
	 */
	private void factHandler(String fact,StringBuffer sql){
		 if(type !=1){
			 String code = fact;
			 sql.append("sum(");
			 if(fact.equals(Constants.LJ_FACT)){
				 code = Constants.BQ_FACT;
			 }else if(fact.equals(Constants.YSLJ_FACT)){
				 code = Constants.YS_FACT;
			 }
			 sql.append(factSplit+code);
			 sql.append(")");
		 }else{
			  sql.append(factSplit+fact);
		 }
	}
	
	/**
	 *  select dim_Item as "编码" , (select sname from DW_DimItem where scode =a.dim_Item ) as "项目",
	 *   fact_A/10000 as "本期数", fact_E/10000 as "上年同期累计"
            from DW_FactFinance
             as a where dim_Period in ('201303')
              and dim_Company in ('100201') 
              and dim_Item in ('1150','1114','1113','112910','11291012','1126') 
              and subject = '0001'
           dispalyMap.put("nlevel", "(select nlevel from DW_Dim"+showDim[0]+" where scode = dim_Item)");
	 */
	public String getSql(DisplayBean displayBean){
		Subject subject = bean.getSubject();
		String  split = subject.getSplit(),//_
			    dim= subject.getDim(),//dim
			    ds=dim+split,
			    fact= subject.getFact();//fact
			    factSplit=fact+split;
		StringBuffer sql  = new StringBuffer("select ");
		Map<String, Compose> composeMap = bean.getComposeMap();
		/**
		 * 如果没有显示维度
		 */
		if(null==showDim||showDim.length==0){
			for (Map.Entry<String, Compose> entry : composeMap.entrySet()){//String key = entry.getKey();//field
				    Compose compose = entry.getValue();
				    set_fact(ds, sql, compose,displayBean);
			}
		}else{
			for(int x=0;x<showDim.length;x++){//String dimS = showDim[x].substring(0,1).toUpperCase()+showDim[x].substring(1);
				Compose compose =composeMap.get(showDim[x]);
				set_fact(ds, sql, compose,displayBean);
			}
		}
		 Map<String, String> dispalyMap = displayBean.getMap();
		 int dismapsize = dispalyMap.size();
		if(dismapsize >0){
			for (Map.Entry<String, String> entry : dispalyMap.entrySet()){//String key = entry.getKey();//field
				String value = entry.getValue();
				sql.append(value).append("as ").append( entry.getKey()).append(",");
		     }
			if(dismapsize > 1){sql.delete(sql.length()-1, sql.length());}
		}
		Map<String, String> measureMap = getMeasureMap();
		if(null==queryFact||queryFact.length ==0){
			for (Map.Entry<String, String> entry : measureMap.entrySet()){
				 String key = entry.getKey();//value = entry.getValue();
				 String factCode = factSplit+key;
				 factHandler(key, sql);
				 if(StringUtil.isBlank(povitDim)){
					 if(!displayBean.isShowSname()){
						 sql.append(" as "+factCode+""); 
					 }else{
						 sql.append(" as \""+entry.getValue()+"\""); 
					 }
				 }
				 sql.append(",");
			}
			sql.delete(sql.length()-1, sql.length());
		}else{
			for (int l = 0; l < queryFact.length; l++) {
				String factCode = queryFact[l],factCodeName = measureMap.get(factCode);
				 if(factCode.indexOf(SymbolConstants.UNDERLINE) !=-1){
					 String []arr =  queryFact[l].split("\\"+SymbolConstants.UNDERLINE);
					 factCode =arr[0];factCodeName = arr[1];
				 }
				 factHandler(factCode, sql);
				 factCode = factSplit+factCode;
				 if(StringUtil.isBlank(povitDim)){
					 if(!displayBean.isShowSname()){
						 sql.append(" as "+factCode);
					 }else{
						 sql.append(" as \""+factCodeName+"\"");
					 }
				 }
				if(l!=queryFact.length-1){
					sql.append(",");
				}
			}
		}
		sql.append("  from ").append(subject.getFactTable());
		/**
		 * 条件开始  一定要有条件维度map
	    */
		if(null !=conditionMap && conditionMap.size() > 0){
			sql.append(" where subject = '"+subject.getScode()+"'");
			for (Map.Entry<String, String> entry : conditionMap.entrySet()){
				String dimKey = entry.getKey(),dimValue = entry.getValue();
				 if(dimValue.contains(SymbolConstants.SEMICOLON)){//有分号  条件数组
					 dimValue =  dimValue.split("\\"+SymbolConstants.SEMICOLON)[0];
				 }
				 StringBuffer value=new StringBuffer();//201301-1月
				 String []temp  = dimValue.split("\\,");//说明传的有多个值
				 for (int l = 0,ll = temp.length; l <ll ; l++) {
						String vv = temp[l];
						if(vv.indexOf("select") != -1){//说明条件是带的子查询语句
							value.append(vv);
						}else{
							if(vv.contains(SymbolConstants.UNDERLINE)){//有别名
								vv = ""+vv.split("\\"+SymbolConstants.UNDERLINE)[0];
							}
							value.append("'").append(vv).append("'");
						}
						
						if(l !=ll-1){
							value.append(",");
						}
				 }
				 sql.append(" and "+ds+dimKey+" in ("+value.toString()+")");
			}
		}
		if(type != 1){//GROUP by dim_Item
			if(null!=orderByDim && orderByDim.length>0){
				sql.append(" GROUP by ");
				for(int x=0,len=orderByDim.length;x<len;x++){
					if(!groupByDim[x].contains("dim_")){
						groupByDim[x]="dim_"+groupByDim[x];
					}
				      sql.append(groupByDim[x]);
				  if(x !=len -1){
					  sql.append(",");
				  }
				}
			}
		}
		if( null != orderByDim){
			if(StringUtil.isBlank(povitDim)){
				  sql.append(" order by ");
				  String DW = GlobalConstants.DIMHELP;
				 for(int x=0,len=orderByDim.length;x<len;x++){
					/**
					 * 如果有&的，则说明是要按照某个维度的某个字段去排序
					 */
					if(orderByDim[x].contains(SymbolConstants.AND)){
						String []temp = orderByDim[x].split("\\"+SymbolConstants.AND);
						sql.append("(select "+temp[1]+" FROM "+DW+temp[0]+" WHERE dim_"+temp[0]+" = scode)");
					}else{
						
					}
					if(x != len-1){
						sql.append(",");
					}
				}
			}
		}
		return sql.toString();
	}
	/**
	 * @return the conditionMap
	 */
	public Map<String, String> getConditionMap() {
		return conditionMap;
	}

	/**
	 * @param conditionMap the conditionMap to set
	 */
	public void setConditionMap(Map<String, String> conditionMap) {
		this.conditionMap = conditionMap;
	}

	/**
	 * @return the queryFact
	 */
	public String[] getQueryFact() {
		return queryFact;
	}

	/**
	 * @param queryFact the queryFact to set
	 */
	public void setQueryFact(String[] queryFact) {
		this.queryFact = queryFact;
	}

	/**
	 * @return the showDim
	 */
	public String[] getShowDim() {
		return showDim;
	}

	/**
	 * @param showDim the showDim to set
	 */
	public void setShowDim(String[] showDim) {
		this.showDim = showDim;
	}

	/**
	 * @return the povitDim
	 */
	public String getPovitDim() {
		return povitDim;
	}

	/**
	 * @param povitDim the povitDim to set
	 */
	public void setPovitDim(String povitDim) {
		this.povitDim = povitDim;
	}

	/**
	 * @return the orderByDim
	 */
	public String[] getOrderByDim() {
		return orderByDim;
	}

	/**
	 * @param orderByDim the orderByDim to set
	 */
	public void setOrderByDim(String[] orderByDim) {
		this.orderByDim = orderByDim;
	}
	/**
	 * @return the groupByDim
	 */
	public String[] getGroupByDim() {
		return groupByDim;
	}

	/**
	 * @param groupByDim the groupByDim to set
	 */
	public void setGroupByDim(String[] groupByDim) {
		this.groupByDim = groupByDim;
	}

	/**
	 * @return the display
	 */
	public DisplayBean getDisplay() {
		return display;
	}

	/**
	 * @param display the display to set
	 */
	public void setDisplay(DisplayBean display) {
		this.display = display;
	}

	/**
	 * @return the measureMap
	 */
	public Map<String, String> getMeasureMap() {
		return measureMap;
	}

	/**
	 * @param measureMap the measureMap to set
	 */
	public void setMeasureMap(Map<String, String> measureMap) {
		this.measureMap = measureMap;
	}

	
	

}
